#region Using
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using CY.GFive.Core.Business;
using CY.Utility.DBUtility;
#endregion Using

namespace CY.GFive.DALProviders.SqlServerProvider
{
	public class MeterialProvider : CY.GFive.Core.Providers.DALProvider.IMeterialProvider
	{
		#region Sql
		private static readonly string SqlSelectMeterial = "SELECT [Id], [ISBN],[Name],[Publish],[MeterialCate],[Des],[Price],[Vision] FROM [Meterial] WHERE  [Id]=@Id";

		private static readonly string SqlInsertMeterial = "INSERT INTO [Meterial]([ISBN],[Name],[Publish],[MeterialCate],[Des],[Price],[Vision]) values(@ISBN,@Name,@Publish,@MeterialCate,@Des,@Price,@Vision);SELECT SCOPE_IDENTITY();";

		private static readonly string SqlUpdateMeterial = "UPDATE [Meterial] SET [ISBN] = @ISBN,[Name] = @Name,[Publish] = @Publish,[MeterialCate] = @MeterialCate,[Des] = @Des,[Price] = @Price,[Vision] = @Vision WHERE  [Id]=@Id";

		private static readonly string SqlDeleteMeterial = "DELETE [Meterial] WHERE  [Id]=@Id";

		private static readonly string SqlGetAllMeterial = "SELECT [Id], [ISBN],[Name],[Publish],[MeterialCate],[Des],[Price],[Vision] FROM [Meterial]";
		#endregion

		#region IMeterialProvider Members
		public Core.Business.Meterial Select(int id)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@Id", SqlDbType.Int, id);
			SqlDataReader reader = sql.ExecuteSqlReader(SqlSelectMeterial);

			if (reader != null && !reader.IsClosed && reader.Read())
			{
				Core.Business.Meterial meterial = new Core.Business.Meterial();

				if (!reader.IsDBNull(0)) meterial.Id = reader.GetInt32(0);
				if (!reader.IsDBNull(1)) meterial.ISBN = reader.GetString(1);
				if (!reader.IsDBNull(2)) meterial.Name = reader.GetString(2);
				if (!reader.IsDBNull(3)) meterial.Publish = reader.GetString(3);
				if (!reader.IsDBNull(4)) meterial.MeterialCate = reader.GetString(4);
				if (!reader.IsDBNull(5)) meterial.Des = reader.GetString(5);
				if (!reader.IsDBNull(6)) meterial.Price = reader.GetDecimal(6);
				if (!reader.IsDBNull(7)) meterial.Vision = reader.GetInt32(7);

				return meterial;
			}
			else
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();

				return null;
			}
		}

		public void Insert(Core.Business.Meterial meterial)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@ISBN", SqlDbType.NVarChar, meterial.ISBN);
			sql.AddParameter("@Name", SqlDbType.NVarChar, meterial.Name);
			sql.AddParameter("@Publish", SqlDbType.NVarChar, meterial.Publish);
			sql.AddParameter("@MeterialCate", SqlDbType.NVarChar, meterial.MeterialCate);
			sql.AddParameter("@Des", SqlDbType.NVarChar, meterial.Des);
			sql.AddParameter("@Price", SqlDbType.Decimal, meterial.Price);
			sql.AddParameter("@Vision", SqlDbType.Int, meterial.Vision);
			SqlDataReader reader = sql.ExecuteSqlReader(SqlInsertMeterial);

			if (reader != null && !reader.IsClosed && reader.Read())
			{
				if (!reader.IsDBNull(0)) meterial.Id = int.Parse(reader.GetDecimal(0).ToString());

				reader.Close();
			}
			else
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void Update(Core.Business.Meterial meterial)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@ISBN", SqlDbType.NVarChar, meterial.ISBN);
			sql.AddParameter("@Name", SqlDbType.NVarChar, meterial.Name);
			sql.AddParameter("@Publish", SqlDbType.NVarChar, meterial.Publish);
			sql.AddParameter("@MeterialCate", SqlDbType.NVarChar, meterial.MeterialCate);
			sql.AddParameter("@Des", SqlDbType.NVarChar, meterial.Des);
			sql.AddParameter("@Price", SqlDbType.Decimal, meterial.Price);
			sql.AddParameter("@Vision", SqlDbType.Int, meterial.Vision);
			sql.AddParameter("@Id", SqlDbType.Int, meterial.Id);
			sql.ExecuteSql(SqlUpdateMeterial);
		}

		public void Delete(Core.Business.Meterial meterial)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@Id", SqlDbType.Int, meterial.Id);
			sql.ExecuteSql(SqlDeleteMeterial);
		}

		public IList<Core.Business.Meterial> GetAllMeterial()
		{
			IList<Core.Business.Meterial> meteriallist = new List<Core.Business.Meterial>();
			SqlServerUtility sql = new SqlServerUtility();

			SqlDataReader reader = sql.ExecuteSqlReader(SqlGetAllMeterial);

			if(reader != null)
			{
				while(reader.Read())
				{
					Core.Business.Meterial meterial = new Core.Business.Meterial();

					if (!reader.IsDBNull(0)) meterial.Id = reader.GetInt32(0);
					if (!reader.IsDBNull(1)) meterial.ISBN = reader.GetString(1);
					if (!reader.IsDBNull(2)) meterial.Name = reader.GetString(2);
					if (!reader.IsDBNull(3)) meterial.Publish = reader.GetString(3);
					if (!reader.IsDBNull(4)) meterial.MeterialCate = reader.GetString(4);
					if (!reader.IsDBNull(5)) meterial.Des = reader.GetString(5);
					if (!reader.IsDBNull(6)) meterial.Price = reader.GetDecimal(6);
					if (!reader.IsDBNull(7)) meterial.Vision = reader.GetInt32(7);

					meterial.MarkOld();
					meteriallist.Add(meterial);
				}
				reader.Close();
			}
			return meteriallist;
		}
		#endregion
	}
}
